<html><body>
<?php

/********************************************************************
This file processes the user-inputed values from hsvisitsurvey.html *
*********************************************************************/

$conn = oci_connect('stran1', 'db4fun', 'oraserver.cs.luc.edu/cs');//connect to database
if (!$conn) 
{
trigger_error("Could not connect to database", E_USER_ERROR);
}//end if


/************************************************************
* Assign user-defined values regarding consortium personnel *
* info to local variables in this program                   *
*************************************************************/

$pfname = $_POST['pfname'];
$plname = $_POST['plname'];
$phone1 = $_POST['phac'];
$phone2 = $_POST['phexch'];
$phone3 = $_POST['phrest'];
$phone4 = $_POST['phext'];
$pphone = $phone1.$phone2.$phone3.$phone4;
$pemail = $_POST['pemail'];
$uname = $_POST['uname'];

/*************************************************************
* Assign user-defined values regarding the high school visit *
* info to local variables in this program                    *
**************************************************************/

$vtype = $_POST['vtype'];
$vmonth = $_POST['vmonth'];
$vday = $_POST['vday'];
$vyear = $_POST['vyear'];
$vyeartrim = substr($vyear, -2);
$vdate = $vday."-".$vmonth."-".$vyeartrim;
$nstudents = $_POST['nstudents'];
$vcomments = $_POST['vcomments'];
$sname = $_POST['sname'];
$scity = $_POST['scity'];
$szip = $_POST['szip'];
$cemail = $_POST['cemail'];
$cvisited = "yes";
$sQuery = oci_parse($conn, "select scode from schools where sname = :school_bv and scity = :scity_bv and szip = :szip_bv");//retrieve scode from the schools table in the database
oci_bind_by_name($sQuery, ":school_bv", $sname);
oci_bind_by_name($sQuery, ":scity_bv", $scity);
oci_bind_by_name($sQuery, ":szip_bv", $szip);
oci_execute($sQuery);
$row = oci_fetch_row($sQuery);
$scode = $row[0];//store the scode into a local variable
oci_free_statement($sQuery);


/********************************************************
* Insert consortium personnel info into the database if *
* no record of them currently exists                    *
*********************************************************/

//check if the personnel is currently in the database
$pemailcheck = oci_parse($conn, "select pemail from personnel where pemail = :pemail_bv");
oci_bind_by_name($pemailcheck, ":pemail_bv", $pemail);
oci_execute($pemailcheck);
$row = oci_fetch_array($pemailcheck);
$numRows = count($row);

//insert new personnel information into database
if($numRows == 1)
{
	oci_free_statement($pemailcheck);
	$pInsert = oci_parse($conn, "insert into personnel values(:pemail_bv, :pphone_bv, :pfname_bv, :plname_bv, :uname_bv)");
	oci_bind_by_name($pInsert, ":pemail_bv", $pemail);
	oci_bind_by_name($pInsert, ":pphone_bv", $pphone);
	oci_bind_by_name($pInsert, ":pfname_bv", $pfname);
	oci_bind_by_name($pInsert, ":plname_bv", $plname);
	oci_bind_by_name($pInsert, ":uname_bv", $uname);
	oci_execute($pInsert);
	oci_free_statement($pInsert);
}//end if


/*********************************************************
* Insert visit info into the database and update a visit *
* if a record of the visit currently exists              *
**********************************************************/

$cemailcheck = oci_parse($conn, "select cemail from contacts where cemail = :cemail_bv");
oci_bind_by_name($cemailcheck, ":cemail_bv", $cemail);
oci_execute($cemailcheck);
$row = oci_fetch_array($cemailcheck);
$numRows = count($row);
if($numRows > 1)
{
	$getvnum = oci_parse($conn, "select max(vnum) from visits");
	oci_execute($getvnum);
	$row = oci_fetch_array($getvnum);
	$vnum = $row[0] + 1;
	oci_free_statement($getvnum);


	//check if the visit already exists in the database
	$visitcheck = oci_parse($conn, "select vnum from visits where vdate = :vdate_bv and pemail = :pemail_bv and cemail = :cemail_bv and scode = :scode_bv");
	oci_bind_by_name($visitcheck, ":vdate_bv", $vdate);
	oci_bind_by_name($visitcheck, ":pemail_bv", $pemail);
	oci_bind_by_name($visitcheck, ":cemail_bv", $cemail);
	oci_bind_by_name($visitcheck, ":scode_bv", $scode);
	oci_execute($visitcheck);
	$row = oci_fetch_array($visitcheck);
	$numRows = count($row);
	$oldvnum = $row[0];

	if($numRows > 1)
	{
		$deletevisit = oci_parse($conn, "delete	from visits where vnum = :oldvnum_bv");
		oci_bind_by_name($deletevisit, ":oldvnum_bv", $oldvnum);
		oci_execute($deletevisit);
		oci_free_statement($deletevisit);

		oci_free_statement($visitcheck);	
		$visitInsert = oci_parse($conn, "insert into visits values(:vnum_bv, :vdate_bv, :vtype_bv, :vcomments_bv, :pemail_bv, :cemail_bv, :nstudents_bv, :cvisited_bv, :scode_bv)");
		oci_bind_by_name($visitInsert, ":vnum_bv", $oldvnum);
		oci_bind_by_name($visitInsert, ":vdate_bv", $vdate);
		oci_bind_by_name($visitInsert, ":vtype_bv", $vtype);
		oci_bind_by_name($visitInsert, ":vcomments_bv", $vcomments);
		oci_bind_by_name($visitInsert, ":pemail_bv", $pemail);
		oci_bind_by_name($visitInsert, ":cemail_bv", $cemail);
		oci_bind_by_name($visitInsert, ":nstudents_bv", $nstudents);
		oci_bind_by_name($visitInsert, ":cvisited_bv", $cvisited);
		oci_bind_by_name($visitInsert, ":scode_bv", $scode);
		oci_execute($visitInsert);
		oci_free_statement($visitInsert);
	}//end if
	else
	{
		oci_free_statement($visitcheck);	
		$visitInsert = oci_parse($conn, "insert into visits values(:vnum_bv, :vdate_bv, :vtype_bv, :vcomments_bv, :pemail_bv, :cemail_bv, :nstudents_bv, :cvisited_bv, :scode_bv)");
		oci_bind_by_name($visitInsert, ":vnum_bv", $vnum);
		oci_bind_by_name($visitInsert, ":vdate_bv", $vdate);
		oci_bind_by_name($visitInsert, ":vtype_bv", $vtype);
		oci_bind_by_name($visitInsert, ":vcomments_bv", $vcomments);
		oci_bind_by_name($visitInsert, ":pemail_bv", $pemail);
		oci_bind_by_name($visitInsert, ":cemail_bv", $cemail);
		oci_bind_by_name($visitInsert, ":nstudents_bv", $nstudents);
		oci_bind_by_name($visitInsert, ":cvisited_bv", $cvisited);
		oci_bind_by_name($visitInsert, ":scode_bv", $scode);
		oci_execute($visitInsert);
		oci_free_statement($visitInsert);
	}//end else

	echo("<h2>Your information has been processed. Thank you!<h2>");

}//end if
else
{
	echo("<h3>The email address for the school contact person you have provided does not match our records<h3>");
}


oci_close($conn);

?>
</body></html>
